﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;

namespace Human
{
	/// <summary>
	/// Summary description for frmTheodoiNS.
	/// </summary>
	public class DS_DangVien : System.Windows.Forms.Form
	{
		private AccessData  mDB=new AccessData();
        Language lan = new Language();
		private DataSet ds =new DataSet();
        private DataSet ds_lst = new DataSet();
		private ExportToExcel p =new ExportToExcel();
        string sql="", s_bophan = "", user_dn = "",user="";
		private System.Windows.Forms.Button butKetThuc;
		private System.Windows.Forms.Button butBaoCao;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.RadioButton rdbMayIn;
		private System.Windows.Forms.RadioButton rdbManHinh;
		private System.Windows.Forms.Label label1;
		private MaskedBox.MaskedBox txtNgay;
		private System.Windows.Forms.RadioButton radexcel;
        private TextBox txt_bophan;
        private Label label2;
        private LibList.List lst_dsdag;
        private TextBox txt_mabp;
		private System.ComponentModel.Container components = null;

		public DS_DangVien(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
            user = mDB.user;
		}
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(DS_DangVien));
            this.butKetThuc = new System.Windows.Forms.Button();
            this.butBaoCao = new System.Windows.Forms.Button();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.radexcel = new System.Windows.Forms.RadioButton();
            this.rdbMayIn = new System.Windows.Forms.RadioButton();
            this.rdbManHinh = new System.Windows.Forms.RadioButton();
            this.txtNgay = new MaskedBox.MaskedBox();
            this.label1 = new System.Windows.Forms.Label();
            this.txt_bophan = new System.Windows.Forms.TextBox();
            this.label2 = new System.Windows.Forms.Label();
            this.lst_dsdag = new LibList.List();
            this.txt_mabp = new System.Windows.Forms.TextBox();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // butKetThuc
            // 
            this.butKetThuc.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butKetThuc.Image = ((System.Drawing.Image)(resources.GetObject("butKetThuc.Image")));
            this.butKetThuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butKetThuc.Location = new System.Drawing.Point(115, 126);
            this.butKetThuc.Name = "butKetThuc";
            this.butKetThuc.Size = new System.Drawing.Size(75, 28);
            this.butKetThuc.TabIndex = 3;
            this.butKetThuc.Text = "Kết thúc";
            this.butKetThuc.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butKetThuc.UseVisualStyleBackColor = true;
            this.butKetThuc.Click += new System.EventHandler(this.butKetThuc_Click);
            // 
            // butBaoCao
            // 
            this.butBaoCao.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butBaoCao.Image = ((System.Drawing.Image)(resources.GetObject("butBaoCao.Image")));
            this.butBaoCao.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butBaoCao.Location = new System.Drawing.Point(36, 126);
            this.butBaoCao.Name = "butBaoCao";
            this.butBaoCao.Size = new System.Drawing.Size(75, 28);
            this.butBaoCao.TabIndex = 2;
            this.butBaoCao.Text = "Báo cáo";
            this.butBaoCao.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butBaoCao.UseVisualStyleBackColor = true;
            this.butBaoCao.Click += new System.EventHandler(this.butBaoCao_Click);
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.radexcel);
            this.groupBox1.Controls.Add(this.rdbMayIn);
            this.groupBox1.Controls.Add(this.rdbManHinh);
            this.groupBox1.Location = new System.Drawing.Point(12, 79);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(232, 41);
            this.groupBox1.TabIndex = 1;
            this.groupBox1.TabStop = false;
            // 
            // radexcel
            // 
            this.radexcel.Location = new System.Drawing.Point(158, 16);
            this.radexcel.Name = "radexcel";
            this.radexcel.Size = new System.Drawing.Size(60, 16);
            this.radexcel.TabIndex = 4;
            this.radexcel.Text = "Excel";
            this.radexcel.KeyDown += new System.Windows.Forms.KeyEventHandler(this.radexcel_KeyDown);
            // 
            // rdbMayIn
            // 
            this.rdbMayIn.Location = new System.Drawing.Point(93, 16);
            this.rdbMayIn.Name = "rdbMayIn";
            this.rdbMayIn.Size = new System.Drawing.Size(72, 18);
            this.rdbMayIn.TabIndex = 3;
            this.rdbMayIn.Text = "Máy in";
            this.rdbMayIn.KeyDown += new System.Windows.Forms.KeyEventHandler(this.rdbMayIn_KeyDown);
            // 
            // rdbManHinh
            // 
            this.rdbManHinh.Checked = true;
            this.rdbManHinh.Location = new System.Drawing.Point(14, 16);
            this.rdbManHinh.Name = "rdbManHinh";
            this.rdbManHinh.Size = new System.Drawing.Size(72, 15);
            this.rdbManHinh.TabIndex = 2;
            this.rdbManHinh.TabStop = true;
            this.rdbManHinh.Text = "Màn hình";
            this.rdbManHinh.KeyDown += new System.Windows.Forms.KeyEventHandler(this.rdbManHinh_KeyDown);
            // 
            // txtNgay
            // 
            this.txtNgay.Font = new System.Drawing.Font("Tahoma", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtNgay.ForeColor = System.Drawing.Color.Black;
            this.txtNgay.Location = new System.Drawing.Point(96, 21);
            this.txtNgay.Mask = "##/##/####";
            this.txtNgay.Name = "txtNgay";
            this.txtNgay.Size = new System.Drawing.Size(72, 23);
            this.txtNgay.TabIndex = 0;
            this.txtNgay.Text = "__/__/____";
            this.txtNgay.Validated += new System.EventHandler(this.txtNgay_Validated);
            this.txtNgay.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtNgay_KeyDown);
            // 
            // label1
            // 
            this.label1.Location = new System.Drawing.Point(12, 21);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(78, 23);
            this.label1.TabIndex = 32;
            this.label1.Text = "Tính đến ngày ";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // txt_bophan
            // 
            this.txt_bophan.Location = new System.Drawing.Point(72, 53);
            this.txt_bophan.Name = "txt_bophan";
            this.txt_bophan.Size = new System.Drawing.Size(180, 20);
            this.txt_bophan.TabIndex = 1;
            this.txt_bophan.TextChanged += new System.EventHandler(this.txt_bophan_TextChanged);
            this.txt_bophan.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txt_bophan_KeyDown);
            this.txt_bophan.MouseClick += new System.Windows.Forms.MouseEventHandler(this.txt_bophan_MouseClick);
            // 
            // label2
            // 
            this.label2.Location = new System.Drawing.Point(12, 50);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(54, 23);
            this.label2.TabIndex = 34;
            this.label2.Text = "Bộ phận :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // lst_dsdag
            // 
            this.lst_dsdag.ColumnCount = 0;
            this.lst_dsdag.FormattingEnabled = true;
            this.lst_dsdag.Location = new System.Drawing.Point(196, 131);
            this.lst_dsdag.MatchBufferTimeOut = 1000;
            this.lst_dsdag.MatchEntryStyle = AsYetUnnamed.MatchEntryStyle.FirstLetterInsensitive;
            this.lst_dsdag.Name = "lst_dsdag";
            this.lst_dsdag.Size = new System.Drawing.Size(54, 17);
            this.lst_dsdag.TabIndex = 35;
            this.lst_dsdag.TextIndex = -1;
            this.lst_dsdag.TextMember = null;
            this.lst_dsdag.ValueIndex = -1;
            this.lst_dsdag.Visible = false;
            // 
            // txt_mabp
            // 
            this.txt_mabp.Location = new System.Drawing.Point(183, 21);
            this.txt_mabp.Name = "txt_mabp";
            this.txt_mabp.Size = new System.Drawing.Size(61, 20);
            this.txt_mabp.TabIndex = 1;
            this.txt_mabp.Visible = false;
            this.txt_mabp.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txt_mabp_KeyDown);
            // 
            // DS_DangVien
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(322, 160);
            this.Controls.Add(this.txt_mabp);
            this.Controls.Add(this.lst_dsdag);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.txt_bophan);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.txtNgay);
            this.Controls.Add(this.butKetThuc);
            this.Controls.Add(this.butBaoCao);
            this.Controls.Add(this.groupBox1);
            this.ForeColor = System.Drawing.Color.DarkBlue;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "DS_DangVien";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Danh sách đảng viên";
            this.Load += new System.EventHandler(this.frmTheodoiNS_Load);
            this.groupBox1.ResumeLayout(false);
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void print(int i)
		{
			try
			{
                DataSet dstmp = new DataSet();
                ExportToExcel exp = new ExportToExcel();
				string a="",s_mmyy="";
                s_mmyy = txtNgay.Text.Trim().Substring(3, 2) + txtNgay.Text.Trim().Substring(8, 2);
                a = lan.Change_language_MessageText("( Tính đến ngày ") + txtNgay.Text.Substring(0, 2).ToString() + lan.Change_language_MessageText(" tháng ") + txtNgay.Text.Substring(3, 2).ToString() + lan.Change_language_MessageText(" năm ") + txtNgay.Text.Substring(6, 4).ToString() + " )";

                sql = " select '" + a + "' as ngay,l.manv as manv,cd.thutu as stt_cd";
                sql += " ,trim(l.ho)||' '||trim(l.ten) as hoten, to_char(l.ngaysinh,'dd/mm/yyyy') as ngaysinh";
                sql += " ,l.phai,dtoc.tendt,tg.tentg,l.quequan,trim(l.noio)||' - '||trim(qu.tenquan)||' - '||trim(tt.tentt) as noio";
                sql += " , to_char(l.dubi_dang,'dd/mm/yyyy') as dubidang,to_char(l.cthuc_dang,'dd/mm/yyyy') as cthucdang";
                sql += " ,hv.tenhv, trim(dt.tenctdt)||' - '||trim(bc.tenbang) as chuyenmon,case when l.chinhtri='SC' then 'Sơ cấp' when l.chinhtri='TC'";
                sql += "  then 'Trung cấp' when l.chinhtri='CN' then 'Cử nhân' when l.chinhtri='CC' then 'Cao cấp' when l.chinhtri='PT' then 'Phổ thông' else '' end as chinhtri";
                sql += " ,ql.ten as qlnn,nt.ten as tinhoc,hd.dang,hd.thedang as sothe,hd.ghichu as ghichu";
                sql += " ,to_char(hd.chuyen_den,'dd/mm/yyyy') as chuyenden,cmax_dang as cv_dang,cmax_quyen as dv_congtac";
                sql += " ,case when trim(hd.chi_bo)<>'' then 'Chi bộ'||' '||trim(hd.chi_bo) else 'Chưa biết chi bộ' end as chibo";
                sql += " ,case when l.mann <> '' then trim(nn1.tennn)||'_'||trim(tn1.ten)  else '' end as ngoaingu";
                sql += " ,case when l.mann1 <> '' then trim(nn2.tennn)||'_'||trim(tn2.ten) else '' end as ngoaingu1";
                sql += " ,case when l.mann2 <> '' then trim(nn3.tennn)||'_'||trim(tn3.ten)  else '' end as ngoaingu2";
                sql += " ,case when l.mann3 <> '' then trim(nn4.tennn)||'_'||trim(tn4.ten) else '' end as ngoaingu3";
                sql += " from " + user + ".llnv l inner join " + user + ".hopdong hd on l.manv =hd.manv ";
                sql += " left join " + user + ".dmcd  cd on cd.ma=l.macd ";
                sql += " left join " + user + ".dantoc dtoc on l.madt=dtoc.madt";
                sql += " left join " + user + ".ctdaotao dt on l.nnghiep=dt.mactdt left join " + user + ".tongiao tg on l.matgiao=tg.matgiao";
                sql += " left join " + user + ".btdtt tt on l.t_noio=tt.matt left join " + user + ".btdquan qu on l.h_noio=qu.maqu";
                sql += " left join " + user + ".hocvan hv on l.mahv=hv.mahv";
                sql += " left join " + user + ".bophan bp on bp.madv=l.madv";
                sql += " left join " + user + ".tdnnth th on l.tdth=th.ma";
                sql += " left join " + user + ".ngoaingu nn on l.mann=nn.mann";
                sql += " left join " + user + ".tdqlnn ql on hd.tdqlnn=ql.ma";
                sql += " left join " + user + ".mahd mh on hd.loaihd=mh.mahd";
                sql += " left join " + user + ".ngoaingu_1 nn1 on l.mann=nn1.mann";
                sql += " left join " + user + ".tdnn1 tn1 on l.tdnn=tn1.ma";
                sql += " left join " + user + ".ngoaingu_2 nn2 on l.mann1=nn2.mann";
                sql += " left join " + user + ".tdnn2 tn2 on l.tdnn1=tn2.ma";
                sql += " left join " + user + ".ngoaingu_3 nn3 on l.mann2=nn3.mann";
                sql += " left join " + user + ".tdnn3 tn3 on l.tdnn2=tn3.ma";
                sql += " left join " + user + ".ngoaingu_4 nn4 on l.mann3=nn4.mann";
                sql += " left join " + user + ".tdnn4 tn4 on l.tdnn3=tn4.ma";

                sql += " left join " + user + ".tdnnth nt on l.tdth=nt.ma inner join " + user + ".max_lan ml on hd.manv=ml.manv ";
                sql += " left join " + user + ".bangcap bc on l.td_nnghiep=bc.mabangcap";
                sql += " where (hd.dang='r' or hd.dang='R') and ";
                sql += " to_date(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_date('" + txtNgay.Text.Trim() + "','dd/mm/yyyy')";
                sql += " and  (hd.ngaynghi is null or to_date(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')>to_date('" + txtNgay.Text.Trim() + "','dd/mm/yyyy'))";
                if (txt_mabp.Text != "")
                    sql += " and bp.madv='"+txt_mabp.Text+"'";
                sql += " and hd.lan=ml.lan order by l.manv,cd.thutu";
                ds=mDB.get_data(sql);
                ds.WriteXml("..\\xml\\h_DanhsachDangvien.xml", XmlWriteMode.WriteSchema);					
				if (ds.Tables[0].Rows.Count>0)
				{
                    switch (i)
                    {
                        case 1: frmReport f = new frmReport(mDB, ds,lan.Change_language_MessageText("DANH SÁCH ĐẢNG VIÊN"), "h_DanhsachDangvien.rpt",user_dn);
                                 f.ShowDialog(this);
                                 break;
                        case 3: dstmp = exp_exel(ds);
                                 exp.exp_exel_dsdangvien(dstmp, "dsdangvien", txtNgay.Text,s_mmyy);
                                 break;
                    }					
				}
				else
					MessageBox.Show(lan.Change_language_MessageText("Chưa có đầy đủ thông tin dữ liệu"),lan.Change_language_MessageText("Thông Báo"));
			}
			catch{}
		}
        public DataSet exp_exel(DataSet dse)
        {

            DataSet dse1 = new DataSet();
            string[] str = new string[] {lan.Change_language_MessageText("STT"),lan.Change_language_MessageText("Chi Bộ"),lan.Change_language_MessageText("Số Thẻ"), lan.Change_language_MessageText("MaNV"), lan.Change_language_MessageText("Họ Tên"),lan.Change_language_MessageText( "Năm Sinh"),lan.Change_language_MessageText( "Ngày Dự Bị"),lan.Change_language_MessageText( "Ngày Chính Thức"),lan.Change_language_MessageText( "Chuyên Môn"),lan.Change_language_MessageText( "Chính Trị"),lan.Change_language_MessageText( "Ngoại Ngữ"),lan.Change_language_MessageText( "Ghi Chú")};
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("chibo", typeof(string));
            dse1.Tables[0].Columns.Add("sothe", typeof(string));
            dse1.Tables[0].Columns.Add("manv", typeof(string));
            dse1.Tables[0].Columns.Add("hoten", typeof(string));
            dse1.Tables[0].Columns.Add("NSnam", typeof(string));
            dse1.Tables[0].Columns.Add("dubidang", typeof(string));
            dse1.Tables[0].Columns.Add("cthucdang", typeof(string));
            dse1.Tables[0].Columns.Add("tenctdt", typeof(string));
            dse1.Tables[0].Columns.Add("chinhtri", typeof(string));
            dse1.Tables[0].Columns.Add("ngoaingu", typeof(string));            
            dse1.Tables[0].Columns.Add("ghichu", typeof(string));
            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables[0].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt.ToString();
                nrow["chibo"] = r["chibo"].ToString();
                nrow["sothe"] = r["sothe"].ToString();
                nrow["manv"] = r["manv"].ToString();
                nrow["hoten"] = r["hoten"].ToString();
                nrow["NSnam"] = r["NSnam"].ToString();
                nrow["dubidang"] = r["dubidang"].ToString();
                nrow["cthucdang"] = r["cthucdang"].ToString();
                nrow["tenctdt"] = r["tenctdt"].ToString();
                nrow["chinhtri"] = r["chinhtri"].ToString();
                nrow["ngoaingu"] = r["ngoaingu"].ToString();                
                nrow["ghichu"] = r["ghichu"].ToString();
                dse1.Tables[0].Rows.Add(nrow);
            }
            return dse1;
        }
        private DataSet  load_bophan()
        {
            ds_lst = mDB.get_data(" select * from " + mDB.user + ".bophan order by madv");
            //txt_bophan.DataSource = dt;
            //txt_bophan.DisplayMember = "tenbp";
            //txt_bophan.ValueMember = "madv";
            //txt_bophan.SelectedIndex = -1;
            return ds_lst;
        }
		private void frmTheodoiNS_Load(object sender, System.EventArgs e)
		{
            txtNgay.Text = DateTime.Now.ToString("dd/MM/yyyy",null);
            ds_lst = load_bophan();
            lst_dsdag.DataSource = ds_lst.Tables[0];
            lst_dsdag.DisplayMember = "madv";
            lst_dsdag.ValueMember = "tenbp";
        
		}
		private void butBaoCao_Click(object sender, System.EventArgs e)
		{
			try
			{
				if(rdbManHinh.Checked)
					print(1);
				else
					if(rdbMayIn.Checked)
					print(1);
				else 
					if(radexcel.Checked ==true)
					print(3);
				butKetThuc.Focus();
			}
			catch(Exception ex){MessageBox.Show(ex.Message);}
		}

		private void txtNgay_Validated(object sender, System.EventArgs e)
		{
			if(!mDB.kiemtrangayhople(txtNgay.Text.Trim()))
				txtNgay.Focus();
		}

		private void butKetThuc_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}

		private void txtNgay_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{           
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
		}

		private void rdbManHinh_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				butBaoCao.Focus();
		}

		private void rdbMayIn_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				butBaoCao.Focus();
		}

		private void radexcel_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				butBaoCao.Focus();
		}

        //private void lst_dsdag_KeyDown(object sender, KeyEventArgs e)
        //{
        //    if (e.KeyCode == Keys.Return)
        //    {
        //        txt_manv_Validated(null, null);
        //    }
        //}
        private void loc_hoten(string bophan)
        {
            try
            {
                CurrencyManager cm = (CurrencyManager)this.BindingContext[lst_dsdag.DataSource];
                DataView dv = (DataView)cm.List;
                dv.RowFilter = "tenbp like '%" + bophan.Trim() + "%' ";
            }
            catch
            {

            }
        }
        private void txt_bophan_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (this.ActiveControl == this.txt_bophan)
                {
                    this.loc_hoten(txt_bophan.Text.Trim());
                    this.lst_dsdag.BrowseToTennv(txt_bophan,txt_mabp, txt_bophan, 500);
                }
            }
            catch { };
        }

        private void txt_bophan_KeyDown(object sender, KeyEventArgs e)
        {
            if ((e.KeyCode == Keys.Down) || (e.KeyCode == Keys.Up))
            {
                this.lst_dsdag.Focus();
            }
            else if (e.KeyCode == Keys.Return)
            {
                if (this.lst_dsdag.Visible)
                {
                    this.lst_dsdag.Focus();
                }
                else
                {
                    SendKeys.Send("{Tab}");
                }
            }
           // rdbManHinh.Focus();
        }

        private void txt_bophan_MouseClick(object sender, MouseEventArgs e)
        {
            ToolTip bp = new ToolTip();
            bp.ShowAlways = true;
            bp.SetToolTip(txt_bophan,lan.Change_language_MessageText( "Nhập bộ phận cần xem !"));
        }

        private void txt_mabp_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }
	}
}
